A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent files or records, but otherwise reserves all copyright rights whatsoever. The following notice shall apply to this document: Copyright (copyright) 1999, Microsoft, Inc.
The present invention pertains generally to computer-implemented databases, and more particularly to mechanisms providing delta and write-back capability to previously written cells of a database.
Online analytical processing (OLAP) is a key part of most data warehouse and business analysis systems. OLAP services provide for fast analysis of multidimensional information. For this purpose, OLAP services provide for multidimensional access and navigation of data in an intuitive and natural way, providing a global view of data that can be drilled down into particular data of interest. Speed and response time are important attributes of OLAP services that allow users to browse and analyze data online in an efficient manner. Further, OLAP services typically provide analytical tools to rank, aggregate, and calculate lead and lag indicators for the data under analysis.
One of the fundamental structures used in OLAP systems is the cube. Cubes are multi-dimensional objects containing measures at specific coordinates specified by dimension members. In this context, a dimension is a structural attribute of a cube that is a list of members of a similar type in the user""s perception of the data. Typically, there is a hierarchy associated with the dimension. For example, a time dimension can consist of days, weeks, months, and years, while a geography dimension can consist of cities, states/provinces, and countries. In the dimension hierarchy, lower members of the hierarchy specify the most detailed data in the dimension, while the upper members of the hierarchy identify less detailed, more aggregated data. Thus the dimension members act as indices for identifying a particular cell or range of cells within a multidimensional array. Each cell contains a value or values, also referred to as a measurement. For example, a measurement can comprise budget or sales data such as dollar amounts and quantity sold.
It is often the case that a user desires to perform xe2x80x9cwhat ifxe2x80x9d analysis using the data provided by OLAP services. In xe2x80x9cwhat-ifxe2x80x9d analysis, a user interactively changes the data value for one or more cell measurements, and the impact of the change is returned to the user. As an example, a finance manager preparing a budget may perform xe2x80x9cwhat-ifxe2x80x9d analysis to answer the question xe2x80x9cwhat will happen to profitability if I increase the research and development budget by 10% and cut marketing by 15%?xe2x80x9d In this situation, the finance manager would want to adjust the appropriate cells in the OLAP database to reflect the appropriate changes to the underlying data. The updated cell values can then be used to derive values for dependent cells higher in the dimension hierarchy that summarize data contained in lower level cells.
In order to perform what-if analysis in previous systems, the user locates the appropriate cell, and writes the adjusted value for the measurement. In the above example, the finance manager would obtain the current budget values, manually determine the new values, and then cause the system to write the new values into the cell measurement data for the marketing budget and the research and development budget.
There are several disadvantages to this process. First, the old cell measurement data is overwritten, and therefore lost. The only way to recover the old value is for the user to either remember what the value was, and manually restore it, or to obtain the value from a database backup (if such a backup exists). In other words, there is no way to automatically xe2x80x9croll backxe2x80x9d a change to the data.
Second, the changes applied by the user are immediately exposed to other users of the system, leading to undesirable effects. For example, assume that two users are using the budget data, the finance manager and a marketing manager. Further assume that the marketing manager wants to determine what the marketing department spent in the last year in order to determine how many new employees to hire. In the above example, after the finance manager has updated the cell data, the changes are also exposed to the marketing manager. The marketing manager will be unaware that the finance manager has updated the data, and will be presented hypothetical, rather than actual data. Thus, the marketing manager""s decisions may be flawed because they are not based on actual data.
As can be seen from the above, there is a need in the art for a system that provides the ability to perform xe2x80x9cwhat-ifxe2x80x9d analysis OLAP databases. The system should provide the ability to change OLAP data without exposing the changes to all users of the system until it is desirable to do so. In addition, the system should provide a means for rolling back selected changes.
The above-mentioned shortcomings, disadvantages and problems are addressed by the present invention, which will be understood by reading and studying the following specification.
The systems and methods described herein provide the ability to maintain data used in performing xe2x80x9cwhat-ifxe2x80x9d analysis on data in a database, such as a cube in a multidimensional OLAP database. One aspect of the system is that changes or updates to base cell values of a cube are maintained in a delta cache. The delta cache maintains the changes as deltas from the base cell measurement value. The delta cache is exposed only to a single client of the system, and disappears when the client is terminated. Thus, the base cube data is not altered or corrupted by the system.
A further aspect of the system is that changes or updates to cell values can be maintained in a write-back partition associated with a multidimensional database. The changes are expressed as deltas in the write-back partition, and are exposed to all clients of the system. Again, since the base cell data remains unchanged, it is not corrupted or written over in an undesirable manner.
Whenever cell data is retrieved, the system scans the delta cache and write-back partition to identify cell values that need to be adjusted by applying the delta values in the cache or write-back partition to the base cell data.
A still further aspect of the system is that the system maintains a data structure comprising cell identification values, cell measurement delta values, a timestamp, and a user name. The timestamp and user name can be used to selectively apply the delta values to cells. For example, a user could choose to apply only those delta values that were set by a particular user, during a particular time period, or a combination of the two.
Thus, embodiments of the invention provide advantages not found in previous systems. The base cell data is left unaltered, with changes to the base cell data held in separate partitions or caches. This greatly reduces the chance of corruption of the base cell data as a result of performing what-if analysis. Additionally, the scope of changes can be controlled. Changes can be exposed to only a single user, to multiple users, or to all users of the system. Furthermore, the changes can be selectively applied or rolled back.
The present invention describes systems, clients, servers, methods, and computer-readable media of varying scope. In addition to the aspects invention described in this summary, further aspects and advantages of the invention will become apparent by reference to the drawings and by reading the detailed description that follows.